package com.cefa.edu.dao;

import com.cefa.edu.datasource.DataSource;
import com.cefa.edu.model.*;
import com.cefa.edu.model.Instructor;
import com.cefa.edu.model.Role;
import com.cefa.edu.model.School;
import com.cefa.edu.model.StudentStageData;
import com.cefa.edu.model.User;
import com.cefa.edu.pojo.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;

import javax.websocket.server.PathParam;
import java.util.List;
import java.util.Map;

import static org.apache.ibatis.jdbc.SqlBuilder.UPDATE;

@Mapper
public interface UserMapper {
    /**
     * @aa 添加监护人用户数据
     * @aa add by chaijian 2018-08-14
     */
    @Options(useGeneratedKeys = true,keyProperty = "id")
    @Insert({"INSERT into cefa_edu_user(name,user_name,password,key_data,id_code,student_id,relationship,type,school_id)" +
            "values(#{name},#{userName},#{password},#{keyData},#{idCode},#{studentId},#{relationship},#{type},#{schoolId})"})
    int saveUserGuardian(com.cefa.edu.pojo.User destUser);

    /**
     * @aa 添加非任课教师用户数据
     * @aa add by chaijian 2018-08-13
     * @return
     */
    @Insert({"insert into cefa_edu_user(name,user_name,password,key_data,id_code,school_id)" +
            "values(#{name},#{userName},#{password},#{keyData},#{idCode},#{schoolId})"})
    @Options(useGeneratedKeys = true,keyProperty = "id")
    int saveUserNotGuardian(com.cefa.edu.pojo.User destUser);


    /**
     * @aa 根据用户身份证号 获取对应的用户信息
     * @aa add by chaijian 2018-08-13
     * @return
     */
    @Results(id = "userMapNew",value = {
            @Result(property = "id",column = "id"),
            @Result(property = "name",column = "name"),
            @Result(property = "userName",column = "user_name"),
            @Result(property = "password",column = "password"),
            @Result(property = "keyData",column = "key_data"),
            @Result(property = "idCode",column = "id_code"),
            @Result(property = "studentId",column = "student_id"),
            @Result(property = "relationship",column = "relationship"),
            @Result(property = "schoolId",column = "school_id"),
            @Result(property = "type",column = "type")
    })
    @Select({"select u.* from cefa_edu_user u where u.id_code=#{idCode}"})
    com.cefa.edu.pojo.User getUserByIdCode_new(String idCode);


    /*=================================================现用user表与之前user表结构有不同==========================================================*/
    /*下面是使用之前user表的sql语句*/
    @DataSource("cefaedu")
//    @Select("SELECT id, name FROM cefa_edu_sample WHERE id = #{id}")
    // Get SQL string from provider
   // @SelectProvider(type= UserProvider.class, method = "getUserString")
   // @Select("select u.id,u.password,u.id_code ,u.student_id,u.name,u.user_name,u.key_data,ur.role_type as roleType,u.school_id  from  cefa_edu_user u left join  cefa_edu_user_organ_role ur on u.id=ur.user_id  where u.password = #{password}  and u.user_name = #{userName} order by ur.role_type asc")
    @Select("select u.id,u.password,u.id_code as idCode ,u.student_id as studentId,u.name,u.user_name as userName,u.key_data as keyData,ur.role_type as roleType,ur.grade_id as gradeId, u.school_id as schoolId  from  cefa_edu_user u ,  cefa_edu_user_organ_role ur where u.id=ur.user_id  and u.password = #{password}  and u.user_name = #{userName} order by ur.role_type asc")
    @Results(id="userMap", value={
            @Result(property = "id", column = "id"),
            @Result(property = "name", column = "name"),
            @Result(property = "userName", column = "user_name"),
            @Result(property = "password", column = "password"),
            @Result(property = "keyData", column = "key_data"),
            @Result(property = "schoolId", column = "school_id"),

            @Result(property = "idCode", column = "id_code"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "relationship", column = "relationship"),
            @Result(property = "type", column = "type"),
            @Result(property = "roleType", column = "role_type"),
            @Result(property = "courseName", column = "course_name"),
            @Result(property = "director", column = "director"),
            @Result(property = "duties", column = "duties"),
            @Result(property = "fileAddress", column = "file_address"),
            @Result(property = "verificationAddress", column = "verification_address")
    })
    List<com.cefa.edu.pojo.User> getUser(@Param("password") String password, @Param("userName") String userName);


    /**
     *
     * @param id 用户id
     * @param order 排序 desc、asc
     * @return
     */
    @DataSource("cefaedu")
    @Select("select u.* ,r.type as roleType,u.school_id as schoolId from  cefa_edu_user u left join  cefa_edu_user_role ur on u.id=ur.user_id left join cefa_edu_role r on ur.role_id=r.id  where u.id=#{id} order by r.type ${order}")
    @ResultMap(value="userMap")
    List<com.cefa.edu.pojo.User> getUserRole(@Param("id") Integer id,@Param("order") String order);

    /**
     *保存用户和层级关系数据
     * @param userId
     * @param levelId
     * @return
     */
    @DataSource("cefaedu")
    @Insert({ "insert into cefa_edu_user_education_level(user_id,level_id,role_type,school_id,duties,status) values(#{userId},#{levelId},#{roleType},#{schoolId},#{duties},#{status})" })
    //@Options(useGeneratedKeys = true, keyProperty = "id")
    int insertUserLevel(@Param("userId") Integer userId,@Param("levelId") Integer levelId,@Param("roleType") Integer roleType,@Param("schoolId") Integer schoolId,@Param("duties") String duties,@Param("status") Integer status);

    /**
     * 保存用户数据
     * @param user
     * @return
     */
    @DataSource("cefaedu")
    @Insert({ "insert into cefa_edu_user(name,user_name,password,key_data,id_code,student_id,relationship,type,school_id,file_address,verification_address,checksum,address,derive_path,mnemonic) values(#{name},#{userName},#{password},#{keyData},#{idCode},#{studentId},#{relationship},#{type},#{schoolId},#{fileAddress},#{verificationAddress},#{checksum},#{address},#{derivePath},#{mnemonic})" })
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertUser(com.cefa.edu.pojo.User user);


    /**
     *
     * @param userId
     * @param roleId
     * @return
     */
    @DataSource("cefaedu")
    @Insert({ "insert into cefa_edu_user_role(user_id,role_id) values(#{userId},#{roleId})" })
    int insertUserRole(@Param("userId") Integer userId,@Param("roleId") Integer roleId);

    /**
     *
     * @param roleType
     * @return
     */
    @DataSource("cefaedu")
    @Select( "select id,role_name as roleName,type from  cefa_edu_role where type=#{roleType}")
    @Results(id="roleMap", value={
            @Result(property = "id", column = "id"),
            @Result(property = "roleName", column = "role_name"),
            @Result(property = "type", column = "type")
    })
    Role getRoleByType(@Param("roleType") Integer roleType);


    /**
     * 保存任课老师数据
     * @param userId
     * @param courseId
     * @param levelId
     * @return
     */
    @DataSource("cefaedu")
    @Insert({ "insert into cefa_edu_instructor(user_id,course_id,level_id,director) values(#{userId},#{courseId},#{levelId},#{director})" })
    int insertInstructor(@Param("userId") Integer userId,@Param("courseId") Integer courseId,@Param("levelId") Integer levelId,@Param("director") Integer director);


    @DataSource("cefaedu")
    @Insert({ "insert into cefa_edu_director(user_id,level_id) values(#{userId},#{levelId})" })
    int insertDirector(@Param("userId") Integer userId,@Param("levelId") Integer levelId);

    /**
     * 根据身份证查询用户信息是否存在
     * @param idCode
     * @return
     */
    @DataSource("cefaedu")
    @Select( "select id, name,user_name,password,key_data,id_code,student_id,relationship,type,school_id from  cefa_edu_user where id_code=#{idCode}")
    @ResultMap(value="userMap")
    com.cefa.edu.pojo.User getUserByIdCode(@Param("idCode") String  idCode);

    @DataSource("cefaedu")
    @Select( "select u.id, u.name,u.user_name,u.password,u.key_data,u.id_code,u.student_id,u.relationship,u.type,u.school_id from  cefa_edu_user u left join cefa_edu_user_education_level ul on u.id=ul.user_id where u.id_code=#{idCode} and ul.level_id=#{levelId}")
    @ResultMap(value="userMap")
    com.cefa.edu.pojo.User getUserByLevelIdAndIdCode(@Param("levelId") Integer  levelId,@Param("idCode") String  idCode);

    @Select( "select id, name,user_name,password,key_data,id_code,student_id,relationship,type,school_id,file_address,verification_address from  cefa_edu_user where id=#{id}")
    @ResultMap(value="userMap")
    com.cefa.edu.pojo.User getUserById(@Param("id") Integer  id);

    @Select( "select id, name,user_name,password,key_data,id_code,student_id,relationship,type,school_id from  cefa_edu_user where school_id=#{schoolId} order by id")
    @ResultMap(value="userMap")
    List<com.cefa.edu.pojo.User> getUserListBySchoolId(@Param("schoolId") Integer  schoolId);

    /**
     * 如果是管理者重复添加，需要根据levelid查询是否重复
     * @param levelId
     * @param idCode
     * @return
     */
    @Select( "select count(1) from cefa_edu_user u left join cefa_edu_user_education_level ul on u.id=ul.user_id where ul.level_id = #{levelId} and u.id_code=#{idCode}")
    int findUserByLevelIdAndIdCode(@Param("levelId") Integer  levelId,@Param("idCode") String  idCode);


    /**
     * 如果是老师 需要额外知道将要添加的任课老师的课程id
     * @param levelId
     * @param idCode
     * @param courseId
     * @return
     */
    @Select( "select count(1) from cefa_edu_user u left join  cefa_edu_user_education_level ul on u.id=ul.user_id left join cefa_edu_instructor i on u.id=i.user_id and i.level_id=ul.level_id  where ul.level_id = #{levelId}  and u.id_code=#{idCode}  and i.course_id=${courseId} ")
    int findUserByLevelIdWithInstructor(@Param("levelId") Integer  levelId,@Param("idCode") String  idCode,@Param("courseId") Integer  courseId);

    /**
     * 如果是班主任，则额外需要班主任表
     * @param levelId
     * @param idCode
     * @return
     */
//    @Select( "select count(1) from cefa_edu_director d left join cefa_edu_user u  on d.user_id=u.id left join cefa_edu_user_education_level  ul on u.id=ul.user_id and ul.level_id=#{levelId} where ul.level_id=8 and u.id_code=#{idCode}")
//    int findUserByLevelIdWithDirector(@Param("levelId") Integer  levelId,@Param("idCode") String  idCode);
    @Select( "select count(1) from cefa_edu_user u left join  cefa_edu_user_education_level ul on u.id=ul.user_id left join cefa_edu_instructor i on u.id=i.user_id and i.level_id=ul.level_id  where ul.level_id = #{levelId}  and u.id_code=#{idCode} and i.director=#{director}")
    int findUserByLevelIdWithDirector(@Param("levelId") Integer  levelId,@Param("idCode") String  idCode,@Param("director") Integer  director);

    /**
     * 任课老师列表（包括老师姓名，任课名称，是否是班主任）
     * @param levelId
     * @return
     */
    @Select("select u.name,c.name as courseName,i.director as director from cefa_edu_instructor i left join cefa_edu_user u on i.user_id=u.id left join  cefa_edu_course c on i.course_id=c.id where i.level_id=${levelId}")
    @Results(id="instructorMap", value={
            @Result(property = "id", column = "id"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "courseId", column = "course_id"),
            @Result(property = "levelId", column = "level_id"),
            @Result(property = "director", column = "director"),

            @Result(property = "courseName", column = "course_name"),
            @Result(property = "name", column = "name")
    })
    List<com.cefa.edu.pojo.Instructor> findDirectorByLevelId (@Param("levelId") Integer  levelId);


    @Select("select count(1) from cefa_edu_instructor WHERE user_id=#{userId} and level_id=#{levelId}")
    int getInstructor(@Param("userId") Integer userId,@Param("levelId") Integer levelId);

    @Select("select count(1) from cefa_edu_user u left join cefa_edu_user_education_level ul on u.id=ul.user_id  WHERE ul.level_id=#{levelId}")
    int getUserCount(@Param("levelId") Integer levelId);

    /**
     * 保存毕业信息数据
     * @param graduation
     * @return
     */
    @Insert({ "insert into cefa_edu_graduation(user_id,grade_name,education_stage_name,school_id,class_id) values(#{userId},#{gradeName},#{educationStageName},#{schoolId},#{classId})" })
    int insertGraduation(Graduation graduation);


    /**
     * 把班级id是levelId2对应的任课老师，关联到班级id是levelId的班级
     * @param levelId
     * @param levelId2
     * @return
     */
    @Update({ "update cefa_edu_instructor set level_id = #{levelId} where level_id=#{levelId2}" })
    int updateInstructorByLevelId(@Param("levelId") Integer levelId,@Param("levelId2") Integer levelId2);

    /**
     * 查询学校的任课老师数量
     * @param schoolId
     * @param roleType1
     * @param roleType2
     * @return
     */
    @Select( "select count(1) from  cefa_edu_user_education_level  where school_id=#{schoolId} and (role_type=#{roleType1} or role_type=#{roleType2}) and status=0")
    int findUserEducationLevelByroleType(@Param("schoolId") Integer  schoolId,@Param("roleType1") Integer  roleType1,@Param("roleType2") Integer  roleType2);

    @Select( "select count(1) from cefa_edu_education_level l left join   cefa_edu_user_education_level ul on l.id=ul.level_id where l.school_id=#{schoolId} and (role_type=#{roleType1} or role_type=#{roleType2}) and l.g_id=#{gId} and ul.status=0")
    int findUserEducationLevelByroleTypeAndgId(@Param("schoolId") Integer  schoolId,@Param("roleType1") Integer  roleType1,@Param("roleType2") Integer  roleType2,@Param("gId") Integer  gId);

    @Select( "select count(1) from cefa_edu_education_level l left join   cefa_edu_user_education_level ul on l.id=ul.level_id where l.school_id=#{schoolId} and (role_type=#{roleType1} or role_type=#{roleType2}) and l.p_id=#{pId} and ul.status")
    int findUserEducationLevelByroleTypeAndpId(@Param("schoolId") Integer  schoolId,@Param("roleType1") Integer  roleType1,@Param("roleType2") Integer  roleType2,@Param("pId") Integer  pId);

    @Select( "select count(1) from cefa_edu_education_level l left join   cefa_edu_user_education_level ul on l.id=ul.level_id where l.school_id=#{schoolId} and (ul.role_type=#{roleType1} or ul.role_type=#{roleType2}) and l.id=#{id} and ul.status")
    int findUserEducationLevelByroleTypeAndId(@Param("schoolId") Integer  schoolId,@Param("roleType1") Integer  roleType1,@Param("roleType2") Integer  roleType2,@Param("id") Integer  id);

    @Select("select id, school_name as schoolName ,address from cefa_edu_school  WHERE id=#{id}")
    @Results(id="schoolMap", value={
            @Result(property = "id", column = "id"),
            @Result(property = "schoolName", column = "school_name"),
            @Result(property = "address", column = "address")
    })
    School  getSchool(@Param("id") Integer id);

    @Insert({ "insert into cefa_edu_student_stage_data(user_id,class_name,grade_name,education_stage_name,education_stage_id,school_id,start_year) values(#{userId},#{className},#{gradeName},#{educationStageName},#{educationStageId},#{schoolId},#{startYear})" })
    int insertStudentStageData(StudentStageData studentStageData);

    /**
     * @param level_id
     * @return
     * 根据level_id查询在校生，同时查出这个学生学习的课程
     */
    @Select("select a.*,a.user_id as userid,b.name from\n" +
            "(select * from cefa_edu_user_education_level where level_id=#{level_id} and role_type=6 and status=0) a \n" +
            "LEFT JOIN\n" +
            "cefa_edu_user b on a.user_id= b.id")
    @Results({
            @Result(property = "courses",column = "userid",
            many = @Many(select = "com.cefa.edu.dao.CourseMapper.findCourseByUserId")
            )
    })
    List<StudentInfo> findStudentName(@Param("level_id") String level_id);


    @Select("select e.*,f.name as sname from \n" +
            "(select c.*,d.name as gname from \n" +
            "(select a.*,b.g_id,b.p_id,b.name as cname from \n" +
            "(select * from cefa_edu_user_education_level where level_id in ( ${level_ids} ) and status=0) a\n" +
            "left join cefa_edu_education_level b  on a.level_id=b.id) c\n" +
            "left join cefa_edu_education_level d  on c.g_id=d.id) e \n" +
            "left join cefa_edu_education_level f  on e.p_id=f.id")
    List<StudentInfo> findStudentInfoByLevelId(@Param("level_ids") String level_ids);

    @Select("select * from cefa_edu_education_level where id in ( \n" +
            "select level_id from cefa_edu_user_education_level where user_id=#{user_id})")
    List<StudentInfo> findLoginUserLevel(@Param("user_id") Integer user_id);

    @Select("select id from cefa_edu_education_level where g_id=#{id}")
    List<Integer> findLevelOne(@Param("id") Integer id);

    @Select("select id from cefa_edu_education_level where p_id=#{id}")
    List<Integer> findLevelTwo(@Param("id") Integer id);

    /**
     * @param searchText
     * @param level_ids
     * @return
     * 根据学生的名字和查询用户的level_id模糊查询此用户权限下面的学生所在班级信息
     */
    @Select("select g.*,h.name from \n" +
            "(select e.*,f.name as education_stage_name from (select c.*,d.name as grade_name from\n" +
            "(select a.*,b.g_id,b.p_id,b.name as class_name from\n" +
            "(select * from cefa_edu_user_education_level where user_id in \n" +
            "(select id from cefa_edu_user where name like #{searchText}) and level_id in \n" +
            "(${level_ids}) and role_type=6 and status=0)a \n" +
            "left join cefa_edu_education_level b on a.level_id=b.id) c \n" +
            "left join cefa_edu_education_level d on c.g_id=d.id) e \n" +
            "left join cefa_edu_education_level f on e.p_id=f.id) g\n" +
            "left JOIN cefa_edu_user h on g.user_id=h.id")
    List<StudentInfo> findStudentLevelId(@Param("searchText") String searchText,@Param("level_ids") String level_ids);

    /**
     *查询学校学生的数量（毕业生除外）
     * @param schoolId
     * @param roleType
     * @return
     */
    @Select( "select count(1) from  cefa_edu_user_education_level  where school_id=#{schoolId} and role_type=#{roleType} and status=0")
    int findUserEducationLevelByroleType2(@Param("schoolId") Integer  schoolId,@Param("roleType") Integer  roleType);


    /**
     * 查询教育阶段的学生数量（毕业生除外）
     * @param schoolId
     * @param roleType
     * @param gId
     * @return
     */
    @Select( "select count(1) from cefa_edu_education_level l left join   cefa_edu_user_education_level ul on l.id=ul.level_id where l.school_id=#{schoolId} and role_type=#{roleType} and l.g_id=#{gId} and ul.status=0")
    int findUserEducationLevelByroleTypeAndgId2(@Param("schoolId") Integer  schoolId,@Param("roleType") Integer  roleType,@Param("gId") Integer  gId);


    /**
     * 查询年级的学生数量（毕业生除外）
     * @param schoolId
     * @param roleType
     * @param pId
     * @return
     */
    @Select( "select count(1) from cefa_edu_education_level l left join   cefa_edu_user_education_level ul on l.id=ul.level_id where l.school_id=#{schoolId} and role_type=#{roleType} and l.p_id=#{pId} and ul.status=0")
    int findUserEducationLevelByroleTypeAndpId2(@Param("schoolId") Integer  schoolId,@Param("roleType") Integer  roleType,@Param("pId") Integer  pId);


    @Select( "select count(1) from cefa_edu_education_level l left join   cefa_edu_user_education_level ul on l.id=ul.level_id where l.school_id=#{schoolId} and ul.role_type=#{roleType} and l.id=#{id} and ul.status=0")
    int findUserEducationLevelByroleTypeAndId2(@Param("schoolId") Integer  schoolId,@Param("roleType") Integer  roleType,@Param("id") Integer  id);


    /**
     * 根据阶层id查询这层的管理者列表
     * @param levelId
     * @return
     */
    @Select("select u.id,u.name,ul.duties from  cefa_edu_user_education_level ul left join cefa_edu_user u on ul.user_id=u.id  where ul.level_id= #{levelId} ")
    @ResultMap(value="userMap")
    List<User> getManagerByLevel(@Param("levelId") Integer levelId);

    /**
     * 根据所在层级，查询不同类型的用户（学生、老师）
     * @param type
     * @param levelId
     * @return
     */
    @Select("select u.id,u.name,u.school_id,u.user_name,r.type from cefa_edu_user u left join cefa_edu_user_role ur on u.id=ur.user_id left join cefa_edu_role r on ur.role_id=r.id left join cefa_edu_user_education_level ul on u.id=ul.user_id where r.type=#{type} and ul.level_id=#{levelId}")
    @ResultMap(value="userMap")
    List<User> getStudentByLevelId(@Param("type") Integer type,@Param("levelId") Integer levelId);

    @Select("select * from cefa_edu_user where id=#{user_id}")
    User findUserById(@Param("user_id") Integer user_id);

    @Select("select i.id,i.level_id,i.course_id from cefa_edu_instructor i left join cefa_edu_user u on i.user_id=u.id  where i.level_id in (${levelIds})")
    @ResultMap(value="instructorMap")
    List<Instructor> findInstructorAndUserByLevelId (@Param("levelIds") String  levelIds);


    /**
     * @param level_id
     * @return
     * 根据level_id查找此level的具体的信息
     */
    @Select("select c.*,d.name as education_stage_name from \n" +
            "(select a.*,b.name as grade_name from \n" +
            "(select id,name as class_name,level,p_id,g_id,grade_date from cefa_edu_education_level where id=#{level_id}) a\n" +
            "left join cefa_edu_education_level b on a.p_id=b.id) c\n" +
            "left join cefa_edu_education_level d on c.g_id=d.id")
    StudentInfo getLevelInfoByLevelId(@Param("level_id") Integer level_id);


    /**
     * @param id
     * @return
     * 根据用户id查询用户的角色信息
     */
    @Select("select a.*,b.role as roleName from \n" +
            "(select * from cefa_edu_user_role  where user_id=4 order by role_id limit 1) a \n" +
            "left join cefa_edu_role b on  a.role_id=b.id;")
    @Results({
            @Result(property = "roleId", column = "role_id"),
            @Result(property = "userId", column = "user_id")
    })
    UserRole  getUserRoleInfo(@Param("user_id") Integer id);

    @Select("select id,user_name as userName from  cefa_edu_user where user_name like CONCAT('%', #{userName}, '%')   order by user_name desc")
    @ResultMap(value="userMap")
    List<com.cefa.edu.pojo.User> getUserPi(@Param("userName") String userName);


    /**
     * 修改用户信息
     * @param userId
     * @param name
     * @param idCode
     * @return
     */
    @Update({ "update cefa_edu_user set name = #{name} , id_code=#{idCode} where id=#{userId}" })
    int updateUserById(@Param("userId") Integer userId,@Param("name") String name,@Param("idCode") String idCode);

    /**
     * 修改用户全部信息
     * @param user
     * @return
     */
    @Update({ "update cefa_edu_user set name = #{name} ,user_name=#{userName}, id_code=#{idCode} ,relationship=#{relationship} where id=#{id}" })
    int updateUserById2(com.cefa.edu.pojo.User user);
    @Update({ "update cefa_edu_user set name = #{name} ,user_name=#{userName}, id_code=#{idCode} ,password=#{password},relationship=#{relationship} where id=#{id}" })
    int updateUserById3(com.cefa.edu.pojo.User user);

   @UpdateProvider(type = UserMapperDao.class,method = "updateUser")
    int updateUserById4(com.cefa.edu.pojo.User user);

    @Delete("DELETE FROM cefa_edu_user_organ_role WHERE user_id = #{userId} and role_type=#{roleType} and phase_id=#{levelId}")
    void deletePhase(@Param("userId") Integer userId,@Param("roleType") Integer roleType,@Param("levelId") Integer levelId);

    @Delete("DELETE FROM cefa_edu_user_organ_role WHERE user_id = #{userId} and role_type=#{roleType} and grade_id=#{levelId}")
    void deleteGrade(@Param("userId") Integer userId,@Param("roleType") Integer roleType,@Param("levelId") Integer levelId);

    @Delete("DELETE FROM cefa_edu_user_organ_role WHERE user_id = #{userId} and role_type=#{roleType} and course_id=#{levelId} and class_id =#{classId}")
    void deleteInstructor(@Param("userId") Integer userId,@Param("roleType") Integer roleType,@Param("levelId") Integer levelId,@Param("classId")Integer classId);

    @Delete("DELETE FROM cefa_edu_user_organ_role WHERE user_id = #{userId} and role_type=#{roleType} and class_id=#{levelId}")
    void deleteClass(@Param("userId") Integer userId,@Param("roleType") Integer roleType,@Param("levelId") Integer levelId);

    /**
     * @aa 根据用户账号 获取密码
     * @aa add by chaijian 2018-08-20
     */
    @Select({"select u.password from cefa_edu_user u where u.user_name=#{userName}"})
    String getPasswordByUserName(String userName);

    /**
     * @aa  根据用户账号和新密码 修改用户原密码
     * @aa add by chaijian 2018-08-20
     */
    @Update({"update cefa_edu_user u set u.password=#{password} where u.user_name=#{userName}"})
    int updatePasswordByUserName(@Param("userName") String userName,@Param("password") String password);

    /**
     * @aa 根据学生id 获取对应的监护人数据接口
     * @aa add by chaijian 2018-08-22
     * @return
     */
    @Select({"select u.id,u.name,u.id_code idCode,u.password,u.user_name userName,u.relationship from cefa_edu_user u ,cefa_edu_user_organ_role uor where uor.user_id=u.id and uor.state=0 and u.student_id=#{studentId}"})
    Map<String,Object> getGuardianByStudentId(Integer studentId);

    /**
     * @aa 获取指定学校的所有毕业生
     * @aa add by chaijian 2018-08-24
     * @return
     */
    @Select({"select count(s.id) from cefa_edu_student_stage_data s where s.graduate=1 and s.school_id=#{schoolId}"})
    Integer getAllCountOfGraduates(Integer schoolId);

    /**
     * @aa 获取指定监护人下的学生编号
     * @aa add by chaijian 2018-09-06
     * @return
     */
    @Select({"select u.student_id studentId from cefa_edu_user u where u.id=#{userId}"})
    List<Object> getStudentIdByGuardianId2(Integer userId);

    @Select({"select u.student_id from cefa_edu_user u where u.id=#{userId}"})
    Integer getStudentIdByGuardianId(Integer userId);

    @Select({"select u.id from cefa_edu_user u where u.id_code=#{idCode}"})
    Integer judgeIdCodeExistByIdCode(String idCode);

    @Select({"select count(uor.id) from cefa_edu_user_organ_role uor where uor.state=0 and uor.user_id=#{userId} and uor.role_type=#{roleType}"})
    Integer getCountOfUserOrganRoleWityIdCodeAndRoleType(@Param("userId") Integer userId, @Param("roleType")Integer roleType);

    @Update({"UPDATE cefa_edu_user SET student_id = NULL,relationship = NULL,type = NULL WHERE id = #{guardianId}"})
    int removeRelationshipOfguardianStuden(Integer guardianId);

    //内部类
    public class UserMapperDao{
        public String updateUser(com.cefa.edu.pojo.User user){

            return new SQL(){{
                UPDATE("cefa_edu_user");
                if(StringUtils.isNotBlank(user.getName())){
                    SET("name = #{name}");
                }
                if(StringUtils.isNotBlank(user.getUserName())){
                    SET("user_name=#{userName}");
                }
                if(StringUtils.isNotBlank(user.getIdCode())){
                    SET("id_code=#{idCode}");
                } if(StringUtils.isNotBlank(user.getPassword())){
                    SET("password=#{password}");
                }
                if(StringUtils.isNotBlank(user.getRelationship())){
                    SET("relationship=#{relationship}");
                }
                WHERE("id=#{id}");
            }}.toString();
        }
    }

}
